package com.baoku.wap.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.baoku.wap.entity.Commont;
import com.baoku.wap.entity.Knowledge;
import com.baoku.wap.entity.Title;
import com.baoku.wap.util.DB;

/**
 * 正常jdbc查询下的 Factory
 * 
 * @author 孔卫佳
 *
 */

public class DateFactory {
	
	private int count;     //总条数
	
	private int pageCount; //总页数
	
	private int pageNo;    //显示页面
	
	private int lastPage;  //上一页
	
	private int nextPage;  //下一页
	
	public int getPageCount() {
		return pageCount;
	}
	
	public int getPageNo() {
		return pageNo;
	}

	public int getLastPage() {
		return lastPage;
	}

	public int getNextPage() {
		return nextPage;
	}
	
	public int getCount() {
		return count;
	}
	private void setPage(int pageNo,int pageSize,int count){
		//计算出总页数
		this.pageCount = (int)Math.ceil((double)count/pageSize); 		
		
		//设置正确的页码
		if (pageNo > this.pageCount) {
			this.pageNo = this.pageCount;
		}else if (pageNo < 1) {
			this.pageNo = 1;
		}else {
			this.pageNo = pageNo;
		}
		
		//设置前后页,前后页超出范围也无所谓,有值钱设置的语句控制.
		this.nextPage = this.pageNo + 1;
		this.lastPage = this.pageNo - 1;	
	}	
	
	//用于得到class2表的name,传入class2表的id
	public String getClassTwo(int classtwo) {
		String str = null;
		Connection conn = DB.getConnection();
		Statement st = DB.getStatement(conn);
		ResultSet rs = null;
		try {
			rs = st.executeQuery("select * from class2 where id = "+classtwo);
			if (rs.next()) {
				str = rs.getString("name");
			}
		} catch (SQLException e) {
			DB.close(conn);
			DB.close(st);
			DB.close(rs);
			e.printStackTrace();
		} finally {
			DB.close(conn);
			DB.close(st);
			DB.close(rs);
		}
		return str;
	}
	
	//用于得到claas2类下的问题list.jsp使用.传入class2的id, 和pageNo.默认每页大小4个.
	public List getList(int classtwo,int pageNo){
		int pageSize = 4;
		
		List list = new ArrayList();
		Connection conn = DB.getConnection();
		Statement st = DB.getStatement(conn);
		ResultSet rs = null;
		try {
			rs = st.executeQuery("select count(*) from knowledge where c2id = "+classtwo);
			int count=0;
			if(rs.next()) {count=rs.getInt(1);}
			this.setPage(pageNo,pageSize,count);
			int page = (this.pageNo-1)*pageSize;
			
			rs = st.executeQuery("select * from knowledge where c2id = "+classtwo+" limit "+page+","+pageSize);
			while(rs.next()){
				Title title = new Title();
				title.setId(rs.getInt("id"));
				title.setTitle(rs.getString("question"));
				list.add(title);

			}
		} catch (SQLException e) {
			DB.close(conn);
			DB.close(st);
			DB.close(rs);
			e.printStackTrace();
		} finally {
			DB.close(conn);
			DB.close(st);
			DB.close(rs);
		}
		return list;
	}
	
	//用于得到查找问题的列表findlist.jsp使用.传入关键字tf,和pageNo.
	public List getFindList (String tf,int pageNo){
		int pageSize = 4;
		
		List list = new ArrayList();
		Connection conn = DB.getConnection();
		Statement st = DB.getStatement(conn);
		ResultSet rs = null;
		try {
			rs = st.executeQuery("select count(*) from knowledge where question LIKE '%"+tf+"%'");
			int count=0;
			if(rs.next()) {count=rs.getInt(1);}
			this.setPage(pageNo,pageSize,count);
			int page = (this.pageNo-1)*pageSize;
			rs = st.executeQuery("select * from knowledge where question LIKE '%"+tf+"%' limit "+page+","+pageSize);
			while(rs.next()){
				Title title = new Title();
				title.setId(rs.getInt("id"));
				title.setTitle(rs.getString("question"));
				list.add(title);

			}
		} catch (SQLException e) {
			DB.close(conn);
			DB.close(st);
			DB.close(rs);
		} finally {
			DB.close(conn);
			DB.close(st);
			DB.close(rs);
		}
		return list;
	}
	
	//返回inner页面要显示的数据.
	public Knowledge getInner(int id){
		Knowledge knowledge = new Knowledge();
		Connection conn = DB.getConnection();
		Statement st = DB.getStatement(conn);
		ResultSet rs = null;
		
		try {
			rs = st.executeQuery("select * from knowledge where id ="+id);
			if(rs.next()){
				knowledge.setQuestion(rs.getString("question"));
				knowledge.setAnswer(rs.getString("answer"));
			}
			
			rs = st.executeQuery("select count(*) from commont where knowledgeid ="+id);
			if(rs.next()){
				knowledge.setAllcom(rs.getInt(1));
			}
			
			rs = st.executeQuery("select * from commont where knowledgeid ="+id+" limit 0,2");
			ArrayList list = new ArrayList();
			while (rs.next()){
				Commont com = new Commont();
				if (rs.getString("userid") == null || rs.getString("userid") ==""){
					com.setUserid("游客");
				}else{
					com.setUserid(rs.getString("userid"));
				}
				com.setCom(rs.getString("content"));
				list.add(com);
			}
			
			knowledge.setComList(list);
		} catch (SQLException e) {
			DB.close(conn);
			DB.close(st);
			DB.close(rs);
			e.printStackTrace();
		}finally {
			DB.close(conn);
			DB.close(st);
			DB.close(rs);
		}
		
		return knowledge;
	}
	
	//com.jsp使用
	public List getComList (int id,int pageNo){
		int pageSize = 4;
		
		List list = new ArrayList();
		Connection conn = DB.getConnection();
		Statement st = DB.getStatement(conn);
		ResultSet rs = null;
		try {
			rs = st.executeQuery("select count(*) from commont where knowledgeid ="+id);
			int count=0;
			if(rs.next()) {count=rs.getInt(1); this.count =count;}
			this.setPage(pageNo,pageSize,count);
			int page = (this.pageNo-1)*pageSize;
			rs = st.executeQuery("select * from commont where knowledgeid ="+id+" limit "+page+","+pageSize);
			while(rs.next()){
				Commont com = new Commont();
				if (rs.getString("userid") == null || rs.getString("userid") ==""){
					com.setUserid("游客");
				}else{
					com.setUserid(rs.getString("userid"));
				}
				com.setCom(rs.getString("content"));
				list.add(com);

			}
		} catch (SQLException e) {
			DB.close(conn);
			DB.close(st);
			DB.close(rs);
			//e.printStackTrace();
		} finally {
			DB.close(conn);
			DB.close(st);
			DB.close(rs);
		}
		return list;
	}
	
	//com页面下 用于得到问题question,传入id
	public String getQuestion(int id) {
		String str = null;
		Connection conn = DB.getConnection();
		Statement st = DB.getStatement(conn);
		ResultSet rs = null;
		try {
			rs = st.executeQuery("select * from knowledge where id = "+id );
			if (rs.next()) {
				str = rs.getString("question");
			}
		} catch (SQLException e) {
			DB.close(conn);
			DB.close(st);
			DB.close(rs);
			e.printStackTrace();
		} finally {
			DB.close(conn);
			DB.close(st);
			DB.close(rs);
		}
		return str;
	}
	
	//得到收藏夹的列表.
	public List getMyList(String id,int pageNo){
		int pageSize = 4;
		
		List list = new ArrayList();
		Connection conn = DB.getConnection();
		Statement st = DB.getStatement(conn);
		ResultSet rs = null;
		try {
			rs = st.executeQuery("select count(*) from favorite left join knowledge on favorite.knowledges = knowledge.id where favorite.userid = '"+id+"'");
			int count=0;
			if(rs.next()) {count=rs.getInt(1);}
			this.setPage(pageNo,pageSize,count);
			int page = (this.pageNo-1)*pageSize;
			
			rs = st.executeQuery("select * from favorite left join knowledge on favorite.knowledges = knowledge.id where favorite.userid = '"+id+"' limit "+page+","+pageSize);
			while(rs.next()){
				Title title = new Title();
				title.setId(rs.getInt("id"));
				title.setTitle(rs.getString("question"));
				list.add(title);

			}
		} catch (SQLException e) {
			DB.close(conn);
			DB.close(st);
			DB.close(rs);
			e.printStackTrace();
		} finally {
			DB.close(conn);
			DB.close(st);
			DB.close(rs);
		}
		return list;
	}
	
}
